package dao;

import entity.CurrentPlaylist;
import util.DatabaseUtil;
import java.sql.*;
import java.util.*;

public class CurrentPlaylistDao {
    
    // 获取用户当前播放列表
    public List<CurrentPlaylist> findByUserId(Long userId) throws SQLException {
        List<CurrentPlaylist> playlist = new ArrayList<>();
        String sql = "SELECT * FROM current_playlist WHERE user_id = ? ORDER BY position ASC";
        
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, userId);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                playlist.add(mapResultSetToCurrentPlaylist(rs));
            }
        }
        return playlist;
    }
    
    // 添加歌曲到播放列表
    public void addSong(Long userId, Long songId) throws SQLException {
        Connection conn = DatabaseUtil.getConnection();
        conn.setAutoCommit(false);
        try {
            // 获取当前最大position
            int maxPosition = 0;
            String maxPositionSql = "SELECT COALESCE(MAX(position), 0) as max_pos FROM current_playlist WHERE user_id = ?";
            try (PreparedStatement stmt = conn.prepareStatement(maxPositionSql)) {
                stmt.setLong(1, userId);
                ResultSet rs = stmt.executeQuery();
                if (rs.next()) {
                    maxPosition = rs.getInt("max_pos");
                }
            }
            
            // 添加新歌曲
            String insertSql = "INSERT INTO current_playlist (user_id, song_id, position) VALUES (?, ?, ?)";
            try (PreparedStatement stmt = conn.prepareStatement(insertSql)) {
                stmt.setLong(1, userId);
                stmt.setLong(2, songId);
                stmt.setInt(3, maxPosition + 1);
                stmt.executeUpdate();
            }
            
            conn.commit();
        } catch (Exception e) {
            conn.rollback();
            throw e;
        } finally {
            conn.setAutoCommit(true);
        }
    }
    
    // 从播放列表中移除歌曲
    public void removeSong(Long userId, Long songId) throws SQLException {
        Connection conn = DatabaseUtil.getConnection();
        conn.setAutoCommit(false);
        try {
            // 获取要删除歌曲的position
            int position = 0;
            String getPositionSql = "SELECT position FROM current_playlist WHERE user_id = ? AND song_id = ?";
            try (PreparedStatement stmt = conn.prepareStatement(getPositionSql)) {
                stmt.setLong(1, userId);
                stmt.setLong(2, songId);
                ResultSet rs = stmt.executeQuery();
                if (rs.next()) {
                    position = rs.getInt("position");
                }
            }
            
            // 删除歌曲
            String deleteSql = "DELETE FROM current_playlist WHERE user_id = ? AND song_id = ?";
            try (PreparedStatement stmt = conn.prepareStatement(deleteSql)) {
                stmt.setLong(1, userId);
                stmt.setLong(2, songId);
                stmt.executeUpdate();
            }
            
            // 更新其他歌曲的position
            String updateSql = "UPDATE current_playlist SET position = position - 1 WHERE user_id = ? AND position > ?";
            try (PreparedStatement stmt = conn.prepareStatement(updateSql)) {
                stmt.setLong(1, userId);
                stmt.setInt(2, position);
                stmt.executeUpdate();
            }
            
            conn.commit();
        } catch (Exception e) {
            conn.rollback();
            throw e;
        } finally {
            conn.setAutoCommit(true);
        }
    }
    
    // 清空播放列表
    public void clear(Long userId) throws SQLException {
        String sql = "DELETE FROM current_playlist WHERE user_id = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, userId);
            stmt.executeUpdate();
        }
    }
    
    // ResultSet映射到实体
    private CurrentPlaylist mapResultSetToCurrentPlaylist(ResultSet rs) throws SQLException {
        CurrentPlaylist currentPlaylist = new CurrentPlaylist();
        currentPlaylist.setUserId(rs.getLong("user_id"));
        currentPlaylist.setSongId(rs.getLong("song_id"));
        currentPlaylist.setPosition(rs.getInt("position"));
        currentPlaylist.setAddedAt(rs.getTimestamp("added_at"));
        return currentPlaylist;
    }

    /**
     * 检查歌曲是否在播放列表中
     */
    public boolean existsInPlaylist(Long userId, Long songId) throws SQLException {
        String sql = "SELECT 1 FROM current_playlist WHERE user_id = ? AND song_id = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, userId);
            stmt.setLong(2, songId);
            ResultSet rs = stmt.executeQuery();
            return rs.next();
        }
    }

    /**
     * 获取用户播放列表中最大的position
     */
    public int getMaxPosition(Long userId) throws SQLException {
        String sql = "SELECT COALESCE(MAX(position), 0) as max_pos FROM current_playlist WHERE user_id = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, userId);
            ResultSet rs = stmt.executeQuery();
            rs.next();
            return rs.getInt("max_pos");
        }
    }

    /**
     * 添加歌曲到播放列表
     */
    public void addSongWithPosition(Long userId, Long songId, int position) throws SQLException {
        String sql = "INSERT INTO current_playlist (user_id, song_id, position) VALUES (?, ?, ?)";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, userId);
            stmt.setLong(2, songId);
            stmt.setInt(3, position);
            stmt.executeUpdate();
        }
    }
} 